/**
 * Created by jackliu on 15/12/16.
 */

var common = require('../../common');
var pool = require('../index');
var mysql = require('mysql');
var ItbBaseModel = require('./ItbBaseModel');
var TgUserPageModel = require('./TgUserPageModel');


// Schema 结构
var TgUserDocModel = function(obj){
    ItbBaseModel.call(this,obj);

  // 以下为表定义数据，需要的字段就填入
  //`doc_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '用户文档id',
  //`tg_id` bigint(20) unsigned NOT NULL COMMENT '提纲模版id',
  //`map_id` bigint(20) unsigned NOT NULL COMMENT '分页模版id',
  //`design_id` bigint(20) unsigned NOT NULL COMMENT '设计模版id',
  //`doc_name` varchar(100) DEFAULT NULL COMMENT '文档名',
  //`user_id` bigint(20) NOT NULL COMMENT '用户id',
  //`user_type` int(1) NOT NULL DEFAULT '0' COMMENT '0后台',
  //`purpose` int(1) NOT NULL DEFAULT '0' COMMENT '0普通文档; 1测试用',
  //`track` int(1) NOT NULL DEFAULT '0' COMMENT '0不统计; 1统计',
  //`data` mediumtext COMMENT '模版内容（除page以外的信息）',
  //`hash_tag` varchar(100) DEFAULT NULL COMMENT '标签',
  //`thumb` text COMMENT '文档缩略图',
  //`size_id` bigint(20) unsigned NOT NULL COMMENT '尺寸',
  //`type_id` bigint(20) unsigned NOT NULL COMMENT '分类',
  // `finetune_data` mediumtext COMMENT '微调数据',
  // `finetune_flg` int(1) NOT NULL DEFAULT '0' COMMENT '0未微调; 1经过微调',
  //`order` int(3) NOT NULL DEFAULT '999' COMMENT '显示顺序',
  //`ver` int(10) NOT NULL DEFAULT '1' COMMENT '版本号',
  //`create_time` datetime NOT NULL,
  //`update_time` datetime NOT NULL,
  //`update_by` varchar(50) NOT NULL DEFAULT '',
  //`del_flg` int(1) NOT NULL DEFAULT '0',

};
TgUserDocModel.prototype = new ItbBaseModel();

// 取得表名
TgUserDocModel.prototype.getTableName = function(){
    return 'tg_user_doc';
}
// 取得查询语句中where 后面匹配主键的条件（需要各派生类进行重载）
TgUserDocModel.prototype.getWhereConditionOfKeyEqual = function(){
    return 'doc_id='+this.doc_id;
}

//------------------------------------------------------------------
/* 获取文档指定天数总的追踪信息.
 * 参数:ids
 * 参数:type (0:取所有信息, 1:取阅读数, 2:取转发数, 3:取收藏数)
 * 参数:period (最近天数)
 * 参数:callback:回调
 * */
TgUserDocModel.prototype.getTrackInfoById=function(callback){
    var THIS = this;
    var tableName = this.getTableName();

    // 从mysql中查询
    pool.pool.getConnection(function(err, connection) {
        var sql = ' SELECT d.doc_id, d.track, A.read_cnt, A.duration, B.relay_cnt ' +
            ' FROM TG_USER_DOC d ';

        // 阅览数
        if (0 == THIS.type || 1 == THIS.type) {
            sql += ' LEFT JOIN ('+
                ' SELECT t.doc_id, count(*) as read_cnt, ' +
                ' ROUND(SUM(CASE WHEN t.last_time - t.start_time = 0 THEN 5 ELSE t.last_time - t.start_time END)/1000) AS duration ' +
                ' FROM itb_browse_info t '+
                ' WHERE t.doc_id IN ( ' + THIS.ids + ' ) AND t.del_flg = 0 ';
            if (THIS.period && THIS.period>0) {
                sql += ' AND to_days(now()) - to_days(t.create_time) < ' + THIS.period;
            }
            sql += ' GROUP BY t.doc_id ) A ON d.doc_id = A.doc_id ';
        }

        // 转发数
        if (0 == THIS.type || 2 == THIS.type) {
            sql += ' LEFT JOIN ( '+
                ' SELECT t.doc_id, count(*) as relay_cnt FROM itb_relay_info t '+
                ' WHERE t.doc_id IN ( ' + THIS.ids + ') AND t.del_flg = 0 ';
            if (THIS.period && THIS.period>0) {
                sql += ' AND to_days(now()) - to_days(t.create_time) < ' + THIS.period;
            }
            sql += ' GROUP BY t.doc_id ) B ON d.doc_id = B.doc_id '
        }

        sql += ' WHERE d.doc_id IN ( ' + THIS.ids + ' ) AND d.del_flg = 0 ORDER BY d.doc_id';

        // 收藏数
        //if (0 == THIS.type || 3 == THIS.type) {
        //    sql += ' UNION '+
        //        ' SELECT 3 as type, count(*) as cnt FROM itb_collect t '+
        //        ' WHERE t.del_flg = 0 AND t.doc_id = ' + THIS.doc_id;
        //    if (THIS.period && THIS.period>0) {
        //        sql += ' AND to_days(now()) - to_days(t.create_time) < ' + THIS.period;
        //    }
        //}

        //var sqlValue = [THIS.doc_id,THIS.doc_id,THIS.doc_id];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);

        // Use the connection
        connection.query(sql, function(error, items) {
            // And done with the connection.
            connection.release();

            // 回调
            callback(error, items);
        });

    });
}
//---------------------------------end-------------------------------

//------------------------------------------------------------------
/* 根据用户id获取文档基本信息(排出掉印品订单相关等用户作品)
 * 参数:callback: 回调
 * 参数 user_id: 用户id
 * 参数 orderBy: 0或空:doc_id顺序, 1:create_time倒序
 * 参数 typeL: 1 海报  2 多页  3 ppt.
 * */
TgUserDocModel.prototype.getDocInfoByUser = function(callback,skipCnt,pageSize,orderBy,typeL){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        // Use the connection
        var sql = ' SELECT t.doc_id, t.tg_id, t.map_id, t.design_id, t.doc_name, t.purpose, t.track, t.thumb AS doc_thumb, ' +
            ' DATE_FORMAT(t.create_time, "%Y-%m-%d %T") as create_time, ' +
            ' DATE_FORMAT(pg.update_time, "%Y-%m-%d %T") as update_time, pg.thumb, ' +
            ' s.width, s.height, t.type_id, p.type_name '+
            ' FROM TG_USER_DOC t, TG_USER_PAGE pg, TG_SIZE s, TG_TYPE p ' +
            ' WHERE t.del_flg = 0 AND t.user_id = ' +THIS.user_id+
            ' AND pg.page_index = 0 AND pg.del_flg = 0 AND t.doc_id = pg.doc_id '+
            ' AND s.del_flg = 0 AND s.size_id = t.size_id ' +
            ' AND p.del_flg = 0 AND p.type_id = t.type_id ' +
            ' AND t.purpose != 2 ';
        if(typeL){
            sql += ' AND p.`top_parent`='+typeL;
        }
        if (orderBy == 1) {
            sql += ' ORDER BY t.create_time DESC ';
        } else {
            sql += ' ORDER BY t.doc_id ';
        }
        if (typeof skipCnt!='undefined' && skipCnt != null
            && typeof pageSize!='undefined' && pageSize != null && pageSize>0){
            sql += " limit " + Number(skipCnt) + "," + Number(pageSize);
        }

        //var sqlValue = [THIS.user_id];
        //sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            //console.log();

            callback(error, result);
        });
    });
}

TgUserDocModel.prototype.getDocInfoByUserCnt = function(callback,typeL){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        // Use the connection
        var sql = ' SELECT count(t.doc_id) AS cnt '+
            ' FROM TG_USER_DOC t, TG_USER_PAGE pg, TG_SIZE s, TG_TYPE p ' +
            ' WHERE t.del_flg = 0 AND t.user_id = ' +THIS.user_id+
            ' AND pg.page_index = 0 AND pg.del_flg = 0 AND t.doc_id = pg.doc_id '+
            ' AND s.del_flg = 0 AND s.size_id = t.size_id ' +
            ' AND p.del_flg = 0 AND p.type_id = t.type_id ';
        if(typeL){
            sql += ' AND p.`top_parent`='+typeL;
        }

        //var sqlValue = [THIS.user_id];
        //sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            //console.log();

            if(result && result.length>0) {
                callback(error, result[0].cnt);
            } else {
                callback(error, 0);
            }
        });
    });
}
//---------------------------------end-------------------------------

//------------------------------------------------------------------
/* 根据文档id获取文档基本信息
 * 参数:callback: 回调
 * 参数 doc_id: 文档id
 * 参数 orderBy: 0或空:doc_id顺序, 1:create_time倒序
 * 参数 withoutPgInfo: 1不关联pg表
 * */
TgUserDocModel.prototype.getDocInfoById = function(callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        // Use the connection
        var sql = ' SELECT t.doc_id, t.tg_id, t.map_id, t.design_id, t.doc_name, t.hash_tag, t.purpose, t.track, t.thumb AS doc_thumb, ' +
            ' t.finetune_flg, t.finetune_data, t.data, t.user_id, ' +
            ' DATE_FORMAT(t.create_time, "%Y-%m-%d %T") as create_time, ' +
            ' DATE_FORMAT(pg.update_time, "%Y-%m-%d %T") as update_time, pg.thumb, pg2.thumb AS design_thumb, ' +
            ' s.width, s.height, t.type_id, p.type_name, p.parent AS typeM, p.top_parent AS typeL '+
            ' FROM TG_USER_DOC t, TG_USER_PAGE pg, TG_SIZE s, TG_TYPE p, TG_DESIGN_PAGE pg2 ' +
            ' WHERE t.del_flg = 0 AND t.doc_id = ' +THIS.doc_id+
            ' AND pg.page_index = 0 AND pg.del_flg = 0 AND t.doc_id = pg.doc_id '+
            ' AND pg2.page_index = 0 AND pg2.del_flg = 0 AND t.design_id = pg2.design_id '+
            ' AND s.del_flg = 0 AND s.size_id = t.size_id ' +
            // ' AND p.del_flg = 0 AND p.type_id = t.type_id ';
            ' AND p.type_id = t.type_id '; // add by wangjun 2018-06-06去掉分类存在限制

        if(THIS.withoutPgInfo==1) {
            sql = ' SELECT t.doc_id, t.tg_id, t.map_id, t.design_id, t.doc_name, t.hash_tag, t.purpose, t.track, t.thumb AS doc_thumb, ' +
                ' t.finetune_flg, t.finetune_data, t.data, t.user_id, ' +
                ' DATE_FORMAT(t.create_time, "%Y-%m-%d %T") as create_time, ' +
                ' DATE_FORMAT(t.update_time, "%Y-%m-%d %T") as update_time, pg2.thumb AS design_thumb, ' +
                ' s.width, s.height, t.type_id, p.type_name, p.parent AS typeM, p.top_parent AS typeL '+
                ' FROM TG_USER_DOC t, TG_SIZE s, TG_TYPE p, TG_DESIGN_PAGE pg2 ' +
                ' WHERE t.del_flg = 0 AND t.doc_id = ' +THIS.doc_id+
                ' AND pg2.page_index = 0 AND pg2.del_flg = 0 AND t.design_id = pg2.design_id '+
                ' AND s.del_flg = 0 AND s.size_id = t.size_id ' +
                // ' AND p.del_flg = 0 AND p.type_id = t.type_id ';
                ' AND p.type_id = t.type_id '; // add by wangjun 2018-06-06去掉分类存在限制
        }

        //var sqlValue = [THIS.user_id];
        //sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            //console.log();

            callback(error, result);
        });
    });
}
//---------------------------------end-------------------------------

//------------------------------------------------------------------
/* 将From-UserId下的文档转移到To-UserId下
 * 参数 user_id_from: 源用户
 * 参数 user_id_to: 目标用户
 * 参数 callback: 回调
 * */
TgUserDocModel.prototype.moveDocByUser=function(callback){
    var THIS = this;
    var tableName = this.getTableName();

    // delete document
    pool.pool.getConnection(function(err, connection) {
        var sql = ' UPDATE TG_USER_DOC SET user_id=?, update_time=sysdate(), update_by=? ' +
            ' WHERE user_id=? AND user_type=1 AND del_flg=0 ';
        var sqlValue = [THIS.user_id_to, THIS.user_id_to, THIS.user_id_from];
        var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            console.log();

            callback(error, result);
        });
    });
}
//---------------------------------end-------------------------------

//------------------------------------------------------------------
/* 获取设计doc的尺寸的信息
 * 参数:design_id
 * 参数:callback:回调
 * */
TgUserDocModel.prototype.findSizeInfo = function(callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        // Use the connection
        var sql = ' SELECT s.* '+
            ' FROM TG_USER_DOC t,  '+
            ' 	    TG_SIZE s  '+
            ' WHERE t.del_flg=0  '+
            ' AND t.doc_id= '+ THIS.doc_id +
            ' AND s.del_flg=0  '+
            ' AND s.size_id=t.size_id  ';


        //var sqlValue = [THIS.type_id];
        //sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            //console.log();

            if(result && result.length>0){
                callback(error, result[0]);
            } else{
                callback(error, null);
            }

        });
    });
}
//--------------------------------end-------------------------------

//------------------------------------------------------------------
/* 根据用户作品id获取“60x160尺寸的展架”的尺寸信息
 * 参数:callback:回调
 * */
TgUserDocModel.prototype.findSizeInfoOf_ZJ_60_160 = function(callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        // Use the connection
        var sql =
                ' select u.`doc_id`,s.*  '+
                ' from tg_user_doc u, '+
                '	`itb_trade_order_detail` o, '+
                '	`itb_print_param` p, '+
                '	`tg_size` s '+
                ' where u.doc_id= '+ THIS.doc_id +
                '	and u.doc_id=o.`user_doc_id` '+
                '	and o.`pp_id`=p.`pp_id` '+
                '	and p.`size_id`=s.`size_id` '+
                '	and s.`size_id`=7 ';

        //var sqlValue = [THIS.type_id];
        //sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            //console.log();

            if(result && result.length>0){
                callback(error, result[0]);
            } else{
                callback(error, null);
            }

        });
    });
}
//--------------------------------end-------------------------------

/**获取作品的提纲微调信息
 * 参数: typeL	0 未分类   1 海报  2 多页  3 ppt. 4, 印品, 6, 社交媒体
 * 参数: designId	模板 id
 * 返回结果: callback,参数如下
 *          error：null 表示无错误
 *          data：结构如下：
 *               {
 *                 "bleedHeight": 36,
 *                 "bleedWidth": 36,
 *                 "coreStructure": {},            // 文档核心结构字符串
 *                 "designId": 738,
 *                 "designNm": "红色线型简约名片",
 *                 "pageCount": 2,
 *                 "pageHeight": 709,
 *                 "pageWidth": 1134,
 *                 "printPxW": 1200, // 印刷用像素值
 *                 "printPxH": 1900, // 印刷用像素值
 *                 "productId": 0,
 *                 "productNm": "",
 *                 "textObjects": “",               //  微调初始化字符串
 *                 "typeS": 40101                   // 三级分类
 *               }
 * 注意：返回的是typeS而不是typeL
 *
 * */
TgUserDocModel.prototype.getTgAndFineTuneInfoOfUserDoc  = function(callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {

        var sql  =
            '	 		SELECT d.data AS coreStructure,  '+
            '              		NULL AS designNm,  '+
            '              		( '+
            '              			SELECT COUNT(p.page_id) '+
            '              			FROM tg_user_page p '+
            '              			WHERE p.`doc_id` =  '+ THIS.doc_id+
            '              			AND p.del_flg = 0 '+
            '              		) AS pageCount,  '+
            '              		d.design_id AS designId,  '+
            '              		d.doc_id AS productId,  '+
            '              		d.doc_name AS productNm,  '+
            '              		d.finetune_data AS textObjects,  '+
            '              		d.type_id AS typeS,  '+
            '              		s.width AS pageWidth,  '+
            '              		s.height AS pageHeight,  '+
            '              		ceil(s.width / s.ratio) AS printPxW,  '+// added for v5.6
            '              		ceil(s.height / s.ratio) AS printPxH, '+// added for v5.6
            '              		s.height AS pageHeight,  '+
            '              		ceil(s.dpi / 25.4 * s.`bleed`) AS bleedWidth, '+
            '              		ceil(s.dpi / 25.4 * s.`bleed`) AS bleedHeight '+
            '              FROM tg_user_doc d,  '+
            //'              	tg_map_doc m,  '+
            '              	tg_size s '+
            '              WHERE d.`doc_id` = '+ THIS.doc_id+
            '              	AND d.del_flg = 0 '+
            //'              	AND d.map_id = m.map_id '+
            //'              	AND m.del_flg = 0 '+
            '              	AND d.size_id = s.size_id '+
            '              	AND s.del_flg = 0';

        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            //console.log();

            if(result && result.length>0){
                callback(error, result[0]);
            }else{
                callback(error, null);
            }

        });
    });
}


//------------------------------------------------------------------
// 拷贝用户文档
// 参数：docId 用户文档id
// 参数：purpose 用于印刷订单时设置为2，否则为0
// 参数：callback 回调，参数如下：
//          error:错误
//          doc_id:拷贝的用户文档id，失败时为0
TgUserDocModel.prototype.copy_user_doc = function(docId, purpose , callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        // Use the connection
        var sql = 'CALL copy_user_doc('+docId+','+purpose+',@newDocId);';

        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log("copy_user_doc result is:\n"+result);
            //  result返回为0个元素的数组结构，每行数据包含：
            //  doc_id:拷贝的用户文档id

            if(error) {
                callback(error, null);
            } else if(!result || result.length==0){
                callback(null, 0);
            } else {
                callback(null, result[0][0].doc_id);
            }
        });
    });
}
//--------------------------------end-------------------------------

//------------------------------------------------------------------
// 根据模版生成user_doc/user_page/user_pc
// 参数：designId 模版id
// 参数：userId 用户id
// 参数：purpose 0普通文档; 1测试用; 2印品订单作品
// 参数：callback 回调
TgUserDocModel.prototype.create_user_doc = function(designId, userId, purpose , callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        // Use the connection
        var sql = 'CALL create_user_doc('+designId+','+userId+','+purpose+',@newDocId);';

        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log("create_user_doc result is:\n"+result);
            //  result返回为0个元素的数组结构，每行数据包含：
            //  doc_id:拷贝的用户文档id

            if(result && result.length>0) {
                callback(null, result[0][0].doc_id);
            } else {
                callback(error, null);
            }
        });
    });
}
//--------------------------------end-------------------------------


//------------------------------------------------------------------
// 取得用户文档对应的模版信息
// 参数：docId 用户文档id
// 参数：callback 回调，参数如下：
//          error:错误
TgUserDocModel.prototype.getDesignInfoByUserDocId = function(docId, callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        // Use the connection
        var sql = ' select dd.* '+
            ' from tg_user_doc ud, '+
            ' 	tg_design_doc dd '+
            ' where ud.`doc_id`= '+ docId+
            ' and ud.`design_id`=dd.`design_id` '+
            ' and ud.`del_flg`=0 '+
            ' and dd.`del_flg`=0 ';

        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log("getDesignInfoByUserDocId result is:\n"+result);

            callback(error, result);
        });
    });
}
//--------------------------------end-------------------------------


//------------------------------------------------------------------
// 取得用户文档对应的模版的设计费
// 参数：docId 用户文档id
// 参数：callback 回调，参数如下：
//          error:错误
//          designFee: 设计费
TgUserDocModel.prototype.findDesignFeeByUserDocId = function(docId, callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        // Use the connection
        var sql = ' select dd.price '+
            ' from tg_user_doc ud, '+
            ' 	tg_design_doc dd '+
            ' where  ud.`doc_id`= '+ docId+
            ' and ud.`design_id`=dd.`design_id` '+
            ' and ud.`del_flg`=0  '+
            ' and dd.`del_flg`=0  ';


        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log("findDesignFeeByUserDocId result is:\n"+result);
            //  result返回为0个元素的数组结构，每行数据包含：
            //  doc_id:拷贝的用户文档id

            if(error) {
                callback(error, null);
            } else if(!result || result.length==0){
                callback(null, 0);
            } else {
                callback(null, result[0].price);
            }
        });
    });
}
//--------------------------------end-------------------------------

//------------------------------------------------------------------
/**
 * 根据用户id获取文档基本信息
 * @param bForCnt: true [仅用于取count]; false [取数据]
 * @param userId: 用户id
 * @param typeL: 0 全部  1 海报  2 多页  3 ppt  4 印品
 * @returns {string}
 * add: 2017-03-10 by: wangJun
 */
function sql_GetUserDocsByTypeL(bForCnt, userId, typeL){

    // 海报文档
    var sql1 = ' SELECT t.doc_id, t.doc_name, t.create_time, t.update_time, '+
        ' pg.thumb, p.top_parent AS top_type '+
        ' FROM TG_USER_DOC t, TG_USER_PAGE pg, TG_SIZE s, TG_TYPE p ' +
        ' WHERE t.del_flg = 0 AND t.purpose=0 AND t.user_id = ' +userId+
        ' AND pg.page_index = 0 AND pg.del_flg = 0 AND t.doc_id = pg.doc_id '+
        ' AND s.del_flg = 0 AND s.size_id = t.size_id ' +
        ' AND p.del_flg = 0  AND p.type_id = t.type_id ';
    //' AND t.purpose != 1 ';
    if(Number(typeL||0)>0){
        sql1 += ' AND p.top_parent='+typeL;
    }

    // PPT文档
    var sql2 = ' SELECT t.doc_id, t.doc_name, t.create_time, t.update_time, '+
        ' p.thumb_s AS thumb, 3 AS top_type ' +
        ' FROM itb_document t, itb_page p '+
        ' WHERE t.del_flg=0 AND t.user_id = '+userId +
        ' AND t.doc_id = p.doc_id ' +
        ' AND p.page_index=0 AND p.del_flg=0 ';

    // 合并集合
    var sql0 =
        ' SELECT null AS doc_id, null AS doc_name, null AS create_time, null AS update_time, '+
        ' null AS thumb, null AS top_type '+
        ' FROM DUAL WHERE 0=1 ';
    if(typeL == 0 || typeL == 1 || typeL == 2 || typeL == 4) {
        sql0 += ' UNION ALL '+ sql1;
    }
    if(typeL == 0 || typeL == 3) {
        sql0 += ' UNION ALL '+ sql2;
    }

    // 总sql
    var sql = ' SELECT tt.* FROM ( ' + sql0 + ' ) tt ORDER BY tt.create_time DESC ';
    if(bForCnt) {
        sql = ' SELECT COUNT(*) AS count FROM ( ' + sql0 + ' ) tt ';
    }

    return sql;
}

function sql_GetUserDocsByTypeL2(bForCnt, userId, typeL){

    // 海报文档
    var sql1 =
        '		SELECT t.doc_id, '+
        ' 				t.doc_name, '+
        ' 				t.create_time, '+
        ' 				t.update_time, '+
        '  				func_fix_get_user_doc_thumb(t.doc_id,0) as thumb,'+
        ' 				p.top_parent AS top_type '+
        '        FROM TG_USER_DOC t, '+
        ' 		 TG_TYPE p  '+
        '        WHERE t.del_flg = 0 '+
        ' 		 AND t.purpose=0 '+
        ' 		 AND t.user_id =  '+ userId+
        '        AND p.del_flg = 0  '+
        ' 		 AND p.type_id = t.type_id';
        '        AND t.purpose != 1 ';
    if(Number(typeL||0)>0){
        sql1 += ' AND p.top_parent='+typeL;
    }


    // PPT文档
    var sql2 = ' SELECT t.doc_id, t.doc_name, t.create_time, t.update_time, '+
        ' p.thumb_s AS thumb, 3 AS top_type ' +
        ' FROM itb_document t, itb_page p '+
        ' WHERE t.del_flg=0 AND t.user_id = '+userId +
        ' AND t.doc_id = p.doc_id ' +
        ' AND p.page_index=0 AND p.del_flg=0 ';

    // 合并集合
    var sql0 =
        ' SELECT null AS doc_id, null AS doc_name, null AS create_time, null AS update_time, '+
        ' null AS thumb, null AS top_type '+
        ' FROM DUAL WHERE 0=1 ';

    if(typeL == 0 || typeL == 1 || typeL == 2 || typeL == 4) {
        sql0 += ' UNION ALL '+ sql1;
    }

    if(typeL == 0 || typeL == 3) {
        sql0 += ' UNION ALL '+ sql2;
    }

    // 总sql
    var sql = ' SELECT tt.* FROM ( ' + sql0 + ' ) tt ORDER BY tt.create_time DESC ';
    if(bForCnt) {
        sql = ' SELECT COUNT(*) AS count FROM ( ' + sql0 + ' ) tt ';
    }

    return sql;
}



//获取用户作品列表sql
function sql_GetUserDocsByTypeL2V6_4(bForCnt, userId, typeL){

    // 海报文档
    var sql1 =
        '		SELECT t.doc_id, '+
        ' 				t.doc_name, '+
        ' 				t.create_time, '+
        ' 				t.update_time, '+
        '  				func_fix_get_user_doc_thumb(t.doc_id,0) as thumb,'+
        ' 				p.top_parent AS top_type '+
        '        FROM TG_USER_DOC t, '+
        ' 		 TG_TYPE p  '+
        '        WHERE t.del_flg = 0 '+
        ' 		 AND t.purpose=0 '+
        ' 		 AND t.user_id =  '+ userId+
        // '        AND p.del_flg = 0  '+
        ' 		 AND p.type_id = t.type_id'+
        '        AND t.purpose != 1 ';
    //if(Number(typeL||0)>0){
    //    sql1 += ' AND p.top_parent='+typeL;
    //}

    if(typeL && typeL.length >0){
        var typeLs = typeL.toString();
        sql1 += ' AND p.top_parent in ('+typeLs+')';
    }

    //// PPT文档
    //var sql2 = ' SELECT t.doc_id, t.doc_name, t.create_time, t.update_time, '+
    //    ' p.thumb_s AS thumb, 3 AS top_type ' +
    //    ' FROM itb_document t, itb_page p '+
    //    ' WHERE t.del_flg=0 AND t.user_id = '+userId +
    //    ' AND t.doc_id = p.doc_id ' +
    //    ' AND p.page_index=0 AND p.del_flg=0 ';

    // 合并集合
    var sql0 =
        ' SELECT null AS doc_id, null AS doc_name, null AS create_time, null AS update_time, '+
        ' null AS thumb, null AS top_type '+
        ' FROM DUAL WHERE 0=1 ';

    if(typeL && typeL.length >0) {
        sql0 += ' UNION ALL '+ sql1;
    }
    //if(typeL == 0 || typeL == 3) {
    //    sql0 += ' UNION ALL '+ sql2;
    //}

    // 总sql
    var sql = ' SELECT tt.* FROM ( ' + sql0 + ' ) tt ORDER BY tt.create_time DESC ';
    if(bForCnt) {
        sql = ' SELECT COUNT(*) AS count FROM ( ' + sql0 + ' ) tt ';
    }

    return sql;
}
//------------------------------------------------------------------
/* 根据用户id获取文档基本信息
 * 参数:callback: 回调
 * 参数 user_id: 用户id
 * 参数 typeL: 0 全部  1 海报  2 多页  3 ppt  4 印品
 * */
TgUserDocModel.prototype.getUserDocsByTypeL = function(userId, typeL, pageNum, pageSize, callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        var sql = ' SELECT x.* FROM ( ' + sql_GetUserDocsByTypeL2(false,userId,typeL) + ' ) x ';

        var skipCnt = Number(pageNum||0)*Number(pageSize||0);
        if(Number(pageSize||0)>0){
            sql += " limit " + skipCnt + "," + Number(pageSize);
        }

        //var sqlValue = [THIS.user_id];
        //sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            //console.log();

            callback(error, result);
        });
    });
};

/* 根据用户id获取文档总个数
 * 参数:callback: 回调
 * 参数 user_id: 用户id
 * 参数 typeL: 0 全部  1 海报  2 多页  3 ppt  4 印品
 * add: 2017-03-10 by: wangJun
 * */
// todo 新增接口 begin
TgUserDocModel.prototype.getUserDocsByTypeLCnt = function(userId, typeL, callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {

        var sql = sql_GetUserDocsByTypeL2(true, userId, typeL);

        //var sqlValue = [THIS.user_id];
        //sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, results) {
            // And done with the connection.
            connection.release();

            //console.log();
            // 回调
            if(results && results.length>0){
                callback(error, results[0].count);
            }else{
                callback(error, 0);
            }
        });
    });
};
// todo 新增接口 end


//------------------------------------------------------------------
/* 根据用户id获取文档基本信息V6_4
 * 参数:callback: 回调
 * 参数 user_id: 用户id
 * 参数 typeL: 0 全部  1 海报  2 多页  3 ppt  4 印品
 * */
TgUserDocModel.prototype.getUserDocsByTypeLV6_4 = function(userId, typeL, pageNum, pageSize, callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        var sql = ' SELECT x.* FROM ( ' + sql_GetUserDocsByTypeL2V6_4(false,userId,typeL) + ' ) x ';

        var skipCnt = Number(pageNum||0)*Number(pageSize||0);
        if(Number(pageSize||0)>0){
            sql += " limit " + skipCnt + "," + Number(pageSize);
        }

        //var sqlValue = [THIS.user_id];
        //sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            //console.log();

            callback(error, result);
        });
    });
};

/* 根据用户id获取文档总个数V6_4
 * 参数:callback: 回调
 * 参数 user_id: 用户id
 * 参数 typeL: 0 全部  1 海报  2 多页  3 ppt  4 印品
 * add: 2017-03-10 by: wangJun
 * */
// todo 新增接口 begin
TgUserDocModel.prototype.getUserDocsByTypeLCntV6_4 = function(userId, typeL, callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {

        var sql = sql_GetUserDocsByTypeL2V6_4(true, userId, typeL);

        //var sqlValue = [THIS.user_id];
        //sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, results) {
            // And done with the connection.
            connection.release();

            //console.log();
            // 回调
            if(results && results.length>0){
                callback(error, results[0].count);
            }else{
                callback(error, 0);
            }
        });
    });
};

//------------------------------------------------------------------
/* 安全删除用户文档
 * 参数 user_id: 用户id
 * 参数 id: 文档id
 * 参数 callback: 回调
 * */
TgUserDocModel.prototype.safeRemoveDoc=function(user_id, id, callback){
    var THIS = this;
    var tableName = this.getTableName();

    // delete document
    pool.pool.getConnection(function(err, connection) {
        var sql = ' UPDATE TG_USER_DOC SET del_flg=1, update_time=sysdate(), update_by=' +user_id+
            ' WHERE user_id=' +user_id+' AND doc_id='+id+' AND del_flg=0 ';
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            console.log();

            if(error){
                return callback(error);
            }
            if(!result){
                return callback(null);
            }
            if(result.affectedRows==0){
                return callback("删除失败");
            }
            return callback(null);
        });
    });
}



//------------------------------------------------------------------
/* 安全删除用户文档
 * 参数: userId	用户唯一标识
 * 参数: orderId	 商品订单 id
 * 参数 callback：回调，参数如下：
 *          error:错误消息
 *          data: 数组，属性如下：
 *                  detail_id:子订单id
 *                  doc_id:用户文档id
 *                  thumbs: 图，多张图以逗号拼接
 * */
TgUserDocModel.prototype.safeGetDocPreviewByOrder=function(userId, orderId, callback){
    var THIS = this;
    var tableName = this.getTableName();

    // delete document
    pool.pool.getConnection(function(err, connection) {
        var sql =
            " select od.detail_id,p.doc_id,GROUP_CONCAT(p.thumb ) as thumbs "+
            " from itb_trade_order_detail od, "+
            "	 tg_user_doc d, "+
            "	 tg_user_page p "+
            " where od.order_id= "+orderId+
            " and od.del_flg=0 "+
            " and od.user_doc_id=d.doc_id  "+
            " and d.del_flg=0 "+
            " and d.user_id= "+userId+
            " and d.del_flg=0 "+
            " and od.user_doc_id=p.doc_id "+
            " and p.del_flg=0 "+
            " group by od.detail_id "+
            " order by p.page_index asc ";

        console.log(sql);
        connection.query(sql, function(error, results) {
            // And done with the connection.
            connection.release();
            console.log();

            if(error){
                return callback(error,null);
            }
            if(!results || results.length==0){
                return callback(null,null);
            }
            return callback(null, results);
        });
    });
};
//---------------------------------end-------------------------------

//------------------------------------------------------------------
/* 获取用户文档
 * 参数: orderId 订单id
 * */
TgUserDocModel.prototype.getOrderUserDoc=function(orderId, callback){
    var THIS = this;
    var tableName = this.getTableName();

    // delete document
    pool.pool.getConnection(function(err, connection) {
        var sql =
            " select *  "+
            " from tg_user_doc d, "+
            " 	`itb_trade_order_detail` o, "+
            " 	`itb_trade_order` t "+
            " WHERE d.del_flg = 0 "+
            " AND d.`purpose` = 2 "+
            " and d.doc_id=o.`user_doc_id` "+
            " and o.del_flg = 0 "+
            " and o.order_id = t.`order_id` "+
            " and t.del_flg = 0 "+
            //" and t.`state`=40 "+
            " and t.order_id="+orderId;

        console.log(sql);
        connection.query(sql, function(error, results) {
            // And done with the connection.
            connection.release();
            console.log();

            return callback(error, results);
        });
    });
};

//------------------------------------------------------------------
/* 获取用户文档
 * 参数: orderId 订单id
 * */
TgUserDocModel.prototype.getOrderUserDoc2=function(orderId, callback){
    var THIS = this;
    var tableName = this.getTableName();

    // delete document
    pool.pool.getConnection(function(err, connection) {
        var sql =
            " select distinct doc_id, `data`,ver   "+
            " from tg_user_doc d, "+
            " 	`itb_trade_order_detail` o, "+
            " 	`itb_trade_order` t "+
            " WHERE d.del_flg = 0 "+
            " AND d.`purpose` = 2 "+
            " and d.doc_id=o.`user_doc_id` "+
            " and o.del_flg = 0 "+
            " and o.order_id = t.`order_id` "+
            " and t.del_flg = 0 "+
                //" and t.`state`=40 "+
            " and t.order_id="+orderId;

        console.log(sql);
        connection.query(sql, function(error, results) {
            // And done with the connection.
            connection.release();
            console.log();

            return callback(error, results);
        });
    });
};


//------------------------------------------------------------------
/* 获取用户文档
 * 参数: tgId	提纲id
 * 参数: pageNum	 页码
 * 参数: pageSize 页数
 * 参数 callback：回调，参数如下：
 *          error:错误消息
 *          data: 数组，元素为TgUserDocModel
 *
 * */
TgUserDocModel.prototype.getTgFinishOrderUserDoc=function(tgId, pageNum, pageSize, callback){
    var THIS = this;
    var tableName = this.getTableName();

    // delete document
    pool.pool.getConnection(function(err, connection) {
        var sql =
            " select *  "+
            " from tg_user_doc d, "+
            " 	`itb_trade_order_detail` o, "+
            " 	`itb_trade_order` t "+
            " WHERE d.del_flg = 0 "+
            " AND d.`tg_id` =  "+tgId+
            " AND d.`purpose` = 2 "+
            " and d.doc_id=o.`user_doc_id` "+
            " and o.del_flg = 0 "+
            " and o.order_id = t.`order_id` "+
            " and t.del_flg = 0 "+
            " and t.`state`=40 "+
            " limit " + Number(pageNum*pageSize) + "," + Number(pageSize);

        console.log(sql);
        connection.query(sql, function(error, results) {
            // And done with the connection.
            connection.release();
            console.log();

            if(error){
                return callback(error,null);
            }
            if(!results || results.length==0){
                return callback(null,null);
            }
            return callback(null, results);
        });
    });
};
//---------------------------------end-------------------------------

//------------------------------------------------------------------
/* 获取某个提纲下的有下单的用户文档的个数
 * 参数: tgId	提纲id
 * 参数 callback：回调，参数如下：
 *          error:错误消息
 *          count: 文档个数
 *
 * */
TgUserDocModel.prototype.getTgFinishOrderUserDocCount=function(tgId, callback){
    var THIS = this;
    var tableName = this.getTableName();

    // delete document
    pool.pool.getConnection(function(err, connection) {
        var sql =
            " select count(*) as `count` "+
            " from tg_user_doc d, "+
            " 	`itb_trade_order_detail` o, "+
            " 	`itb_trade_order` t "+
            " WHERE d.del_flg = 0 "+
            " AND d.`tg_id` =  "+tgId+
            " AND d.`purpose` = 2 "+
            " and d.doc_id=o.`user_doc_id` "+
            " and o.del_flg = 0 "+
            " and o.order_id = t.`order_id` "+
            " and t.del_flg = 0 "+
            " and t.`state`=40 ";

        console.log(sql);
        connection.query(sql, function(error, results) {
            // And done with the connection.
            connection.release();
            console.log();

            if(error){
                return callback(error,null);
            }
            if(results && results.length>0){
                return callback(error, results[0].count);
            }else{
                return callback(error, 0);
            }
        });
    });
};
//---------------------------------end-------------------------------

/**
 * 根据作品 id 数组获取作品数据
 *
 * @author 王俊
 * @date 2018-05-31
 *
 * @param docIds 作品 id 数组
 * @param callback 回调
 *          error: 失败
 *          results: tg_user_doc + tg_size 行数据数组
 */
TgUserDocModel.prototype.getDocumentsInfoByDocIds = function (docIds, callback) {

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('TgUserDocModel.getDocsInfo Err:'+err);
            return callback(err,null);
        }
        // Use the connection
        var ids = docIds.join(',');
        var sql = ' SELECT tud.* , ' +
            '        tdd.data AS design_data, ' +
            '        tdd.ver AS design_ver, ' +
            '        ts.width, ' +
            '        ts.height, ' +
            '        ts.bleed, ' +
            '        ts.print_w, ' +
            '        ts.print_h, ' +
            '        ts.dpi, ' +
            '        ts.ratio ' +
            ' FROM tg_user_doc tud, ' +
            '      tg_size ts, ' +
            '      tg_design_doc tdd ' +
            ' WHERE tud.del_flg = 0 ' +
            '   AND tud.doc_id IN ('+ids+') ' +
            '   AND ts.del_flg=0 ' +
            '   AND ts.size_id = tud.size_id ' +
            '   AND tdd.del_flg = 0 ' +
            '   AND tdd.design_id = tud.design_id ';
        console.log(sql);
        connection.query(sql, function(error, results) {
            // And done with the connection.
            connection.release();
            callback(error, results);
        });
    });
};


// 导出
module.exports = TgUserDocModel;